package dataLayer;

import interfaces.IDAOTeacher;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.TreeSet;

import model.Teacher;

public class DAOTeacher extends DBconnect implements IDAOTeacher {
	TreeSet<Teacher> teacherset = new TreeSet<Teacher>();
	TreeSet<Teacher> teachersetdel = new TreeSet<Teacher>();

	private static DAOTeacher single;

	// Make SQL query and call to DB to addTeacher
	public void insertTeacher(Teacher t) {
		try {
			openConn();
			Statement stmt = getConn().createStatement();
			String sql = "call sp_add_teacher('" + t.getTeacher_name() + "', '"
					+ t.getTeacher_code() + "')";
			stmt.execute(sql);
			stmt.close();
			closeConn();
		} catch (Exception e) {
			System.err.println("SQL Error Dao insertTeacher");

		}
	}

	public void loadTeacher(Teacher g) {

	}

	// Make SQL query and call to DB to EditTeacher
	public void updateTeacher(Teacher t1) {
		try {
			openConn();
			Statement stmt = getConn().createStatement();
			String sql = "call sp_update_teacher('" + t1.getTeacher_oldCode()
					+ "', '" + t1.getTeacher_code() + "', '"
					+ t1.getTeacher_name() + "' " + ")";
			stmt.execute(sql);
			stmt.close();
			closeConn();
		} catch (Exception e) {
			System.err.println("SQL Error Dao updateteacher");

		}

	}

	// Make SQL query and call to DB to deleteTeacher
	public void deleteTeacher(Teacher t) {
		try {
			openConn();
			Statement stmt = getConn().createStatement();
			String sql = "call sp_delete_teacher('" + t.getTeacher_code()
					+ "')";

			stmt.execute(sql);
			stmt.close();
			closeConn();

		} catch (Exception e) {
			System.err.println("SQL Error Dao deleteTeacher");

		}

	}

	// Make SQL query and call to DB to loadAllTeachers
	public TreeSet<Teacher> loadallactiveTeacher() {
		try {
			openConn();
			Statement stmt = getConn().createStatement();
			ResultSet rs = stmt
					.executeQuery("call sp_load_all_active_teachers");

			teacherset.clear();

			Teacher geen = new Teacher(null, "GEEN", "geen");
			teacherset.add(geen);
			Teacher zelfstandig = new Teacher(null, "ZLFS",
					"zelfstandig werken");
			teacherset.add(zelfstandig);

			while (rs.next()) {
				Teacher t = new Teacher(null, null, null);

				t.setTeacher_code(rs.getString("code"));
				t.setTeacher_name(rs.getString("name"));
				teacherset.add(t);

			}
			stmt.close();
			closeConn();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println("SQL Error Dao Loadallactiveteachers");
		}
		return teacherset;
	}

	@Override
	public TreeSet<Teacher> loadalldeletedTeacher() {
		try {
			openConn();
			Statement stmt = getConn().createStatement();
			ResultSet rs = stmt
					.executeQuery("call sp_load_all_deleted_teachers");

			teachersetdel.clear();

			while (rs.next()) {
				Teacher t = new Teacher(null, null, null);

				t.setTeacher_code(rs.getString("code"));
				t.setTeacher_name(rs.getString("name"));

				teachersetdel.add(t);

			}
			stmt.close();
			closeConn();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println("SQL Error Dao Loadalldeletedteachers");
		}
		return teachersetdel;
	}

	public static DAOTeacher getInstance() {
		if (single == null) {
			single = new DAOTeacher();

		}
		return single;
	}

}
